package com.edu.hre.db.sql.admin;

import java.io.Serializable;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;

import com.edu.hre.common.utils.CommonUtils;
import com.edu.hre.db.pojo.HreLocation;
import com.edu.hre.db.service.BaseService;
import com.edu.hre.db.utils.Compare;
import com.edu.hre.struts.form.admin.LocationListForm;
import com.edu.hre.struts.form.admin.OrgLocationForm;

public class LocationDAOService implements Serializable {
	
	/**
	 * 
	 */
	private static final long serialVersionUID = 3394301416150421253L;
	public static final String BEAN_NAME = "locationDAOService";
	private BaseService baseService  = null;

	public void setBaseService(BaseService baseService) {
		this.baseService = baseService;
	} 

	public ArrayList<OrgLocationForm> getLocationListInfo(LocationListForm form, int limit, int offset) {

		ArrayList<OrgLocationForm> listResult = new ArrayList<OrgLocationForm>();
		String sql = "SELECT loc.id AS col1," +
					" loc.name AS col2," +
					" dis.district_name AS col3," +
					" pro.province_vn_name AS col4, " +
					" loc.phone AS col5, " +
					" dis.district_type AS col6, " +
					" loc.address AS col7 " +
					
					" FROM hre_location AS loc" +
					" LEFT OUTER JOIN hre_province AS pro ON loc.province = pro.province_code" +
					" LEFT OUTER JOIN hre_district AS dis ON loc.district = dis.district_code" +
					
					" WHERE 1 = 1";
		
		ArrayList<Object> params = new ArrayList<Object>();
		
		if (form.getName()!=null && !form.getName().equals("")) {
			sql += " AND loc.name = ?";
			params.add(form.getName());
		}
		if (form.getSelectedDistrict()!=null&& !form.getSelectedDistrict().equals("") && !form.getSelectedDistrict().equals("All")) {
			sql += " AND loc.district = ?";
			params.add(form.getSelectedDistrict());
		}
		if (form.getSelectedProvince()!=null && !form.getSelectedProvince().equals("") && !form.getSelectedProvince().equals("All")) {
			sql += " AND loc.province = ?";
			params.add(form.getSelectedProvince());
		}

		sql += " ORDER BY loc.date_created DESC";
		
		List <Object[]> listObject = baseService.listArrayObject(sql, limit, offset, params.toArray());
		for(int i = 0; listObject != null && i < listObject.size(); i++) {
			
			try {

				Object[] objs = listObject.get(i);
				OrgLocationForm locationInfo = new OrgLocationForm();

				locationInfo.setId(CommonUtils.normalizeObjectToString(objs[1], "0"));
				locationInfo.setName(CommonUtils.normalizeObjectToString(objs[2], ""));
				locationInfo.setDistrict(CommonUtils.normalizeObjectToString(objs[3], ""));
				locationInfo.setProvince(CommonUtils.normalizeObjectToString(objs[4], ""));
				locationInfo.setPhone(CommonUtils.normalizeObjectToString(objs[5], ""));

				String districtType = CommonUtils.normalizeObjectToString(objs[6], "");
				locationInfo.setDistrict(districtType + " " + locationInfo.getDistrict());
				locationInfo.setNumOfEmps(countEmployeesOfLocation(CommonUtils.parseInt(locationInfo.getId(), 0)));
				
				locationInfo.setAddress(CommonUtils.normalizeObjectToString(objs[7], ""));
				
				listResult.add(locationInfo);
			} catch (Exception e) {
				CommonUtils.println(e.getMessage());
			}
		}
		
		return listResult;
	}
	
	public int countLocations(LocationListForm form){

		String sql = "SELECT COUNT(*) AS col1" +
				
					" FROM hre_location AS loc" +
					" LEFT OUTER JOIN hre_province AS pro ON loc.province = pro.province_code" +
					" LEFT OUTER JOIN hre_district AS dis ON loc.district = dis.district_code" +
					
					" WHERE 1 = 1";
	
		ArrayList<Object> params = new ArrayList<Object>();
		
		if (form.getName()!=null && !form.getName().equals("")) {
			sql += " AND loc.name = ?";
			params.add(form.getName());
		}
		if (form.getSelectedDistrict()!=null&& !form.getSelectedDistrict().equals("") && !form.getSelectedDistrict().equals("All")) {
			sql += " AND loc.district = ?";
			params.add(form.getSelectedDistrict());
		}
		if (form.getSelectedProvince()!=null && !form.getSelectedProvince().equals("") && !form.getSelectedProvince().equals("All")) {
			sql += " AND loc.province = ?";
			params.add(form.getSelectedProvince());
		}
	
		try {
			List <Object[]> listObject = baseService.listArrayObject(sql, 0, 0, params.toArray());
			Object o [] = listObject.get(0);
			return ((BigInteger) o[1]).intValue();
		} catch (Exception e) {
			CommonUtils.println(e.getMessage());
		}

		return 0;
	}

	public int countEmployeesOfLocation(int locationId){
		
		String sql = "SELECT COUNT(emp.emp_number) AS col1" +

					" FROM hre_emp_jobs AS empjob" +
					" LEFT OUTER JOIN hre_employee AS emp ON empjob.emp_number = emp.emp_number " +
					
					" WHERE empjob.location_id = ? " +
					" AND empjob.terminated_status = 0 " +
					" AND emp.is_deleted = 0";

		try {

			List <Object[]> listObject = baseService.listArrayObject(sql, 0, 0, locationId);
			
			Object o [] = listObject.get(0);
			return ((BigInteger) o[1]).intValue();

		} catch (Exception e) {
			CommonUtils.println(e.getMessage());
		}
		
		return 0;
	}

	public void deleteLocations(LocationListForm form){

		ArrayList<OrgLocationForm> info = (ArrayList<OrgLocationForm>) form.getAllLocations();
		for (int i = 0; i < info.size(); i++) {
			
			try {

				OrgLocationForm location = info.get(i);
				if (location.isSelected()) {
					baseService.deleteFirstPojo("id", Compare.EQUAL, Integer.parseInt(location.getId()), null, null, 0, HreLocation.class);
				}
			} catch (Exception e) {
				CommonUtils.println(e.getMessage());
			}
		}
	}
}
